import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
import os
from datetime import datetime

# 设置中文字体
plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

# 定义常量 - 保存结果的目录
RESULTS_DIR = r"D:\大三上\大数据分析及数据可视化\《Excel数据可视化 - 从图表到数据大屏》-清华-郭宏远\实验\results"


def load_erp_data():
    """加载ERP订单数据"""
    # 定义可能的文件路径
    possible_paths = [
        r"D:\大三上\大数据分析及数据可视化\《Excel数据可视化 - 从图表到数据大屏》-清华-郭宏远\实验\data\erp_order_data.xlsx",
        r"D:\大三上\大数据分析及数据可视化\《Excel数据可视化 - 从图表到数据大屏》-清华-郭宏远\实验\erp_order_data.xlsx"
    ]

    for path in possible_paths:
        if os.path.exists(path):
            try:
                df = pd.read_excel(path)
                # 确保日期格式正确
                if 'order_time' in df.columns:
                    df['order_time'] = pd.to_datetime(df['order_time'])
                print(f"✓ 数据加载成功: {path}")
                return df
            except Exception as e:
                print(f"读取 {path} 失败: {e}")
                continue

    raise FileNotFoundError("未找到erp_order_data.xlsx文件，请确认文件是否存在或路径是否正确")


def process_monthly_sales(df):
    """处理月度销售数据，对比2024年和2025年销量"""
    # 确保order_time是datetime类型
    if 'order_time' not in df.columns:
        raise ValueError("数据中缺少'order_time'列，无法进行时间分析")

    # 确保product_amount列存在
    if 'product_amount' not in df.columns:
        raise ValueError("数据中缺少'product_amount'列，无法计算销售金额")

    # 创建副本防止修改原始数据
    df = df.copy()

    # 提取年份和月份
    df['year'] = df['order_time'].dt.year
    df['month'] = df['order_time'].dt.month

    # 获取数据中的年份
    years = sorted(df['year'].unique())

    # 选择最近的两个年份
    if len(years) < 2:
        raise ValueError("数据中年份不足，需要至少两年的数据进行对比")

    year1 = years[-2]  # 倒数第二年
    year2 = years[-1]  # 最新年份

    print(f"分析年份: {year1}年 和 {year2}年")

    # 按年月分组汇总销量
    monthly_sales = df.groupby(['year', 'month'])['product_amount'].sum().reset_index()

    # 重命名列
    monthly_sales.columns = ['year', 'month', 'sales']

    # 分离两个年份的数据
    sales_year1 = monthly_sales[monthly_sales['year'] == year1]
    sales_year2 = monthly_sales[monthly_sales['year'] == year2]

    # 重命名列以便合并
    sales_year1 = sales_year1.rename(columns={'sales': f'sales_{year1}'})
    sales_year2 = sales_year2.rename(columns={'sales': f'sales_{year2}'})

    # 按月合并
    merged = pd.merge(
        sales_year1[['month', f'sales_{year1}']],
        sales_year2[['month', f'sales_{year2}']],
        on='month',
        how='outer'
    ).fillna(0)

    # 确保所有月份都存在
    all_months = pd.DataFrame({'month': range(1, 13)})
    merged = pd.merge(all_months, merged, on='month', how='left').fillna(0)

    # 按月份排序
    merged = merged.sort_values('month').reset_index(drop=True)

    # 计算同比增长率（处理分母为零的情况）
    merged['growth_rate'] = np.where(
        merged[f'sales_{year1}'] == 0,
        np.where(merged[f'sales_{year2}'] == 0, 0, 999),
        ((merged[f'sales_{year2}'] - merged[f'sales_{year1}']) / merged[f'sales_{year1}']) * 100
    )

    # 添加月份中文标签
    merged['month_label'] = merged['month'].apply(lambda x: f'{x}月')

    return merged, year1, year2


def create_comparison_line_chart():
    """创建对比折线图：2025年上半年各月同比2024年销量"""
    # 加载数据
    df = load_erp_data()

    # 处理月度销售数据
    monthly_sales, year1, year2 = process_monthly_sales(df)

    # 提取数据
    months = monthly_sales['month'].tolist()
    sales_prev = monthly_sales[f'sales_{year1}'].tolist()
    sales_current = monthly_sales[f'sales_{year2}'].tolist()
    growth_rates = monthly_sales['growth_rate'].tolist()
    month_labels = monthly_sales['month_label'].tolist()

    # 创建图形 - 深色背景
    fig, ax = plt.subplots(figsize=(14, 9), facecolor='#1A1A2E')
    ax.set_facecolor('#1A1A2E')

    # 创建X轴位置
    x_pos = np.arange(len(months))

    # 设置Y轴范围
    max_sales = max(max(sales_prev), max(sales_current)) * 1.15
    ax.set_ylim(0, max_sales)

    # 绘制上一年折线（红色）
    line1, = ax.plot(x_pos, sales_prev, marker='o', markersize=8,
                     color='#E56A72', linewidth=3, markeredgecolor='#FFFFFF', markeredgewidth=1.5)

    # 绘制本年折线（蓝色）
    line2, = ax.plot(x_pos, sales_current, marker='o', markersize=8,
                     color='#4BB5C2', linewidth=3, markeredgecolor='#FFFFFF', markeredgewidth=1.5)

    # 添加数据标签
    for i, (sale_prev, sale_current) in enumerate(zip(sales_prev, sales_current)):
        # 上一年标签
        if sale_prev > 0:
            ax.text(i, sale_prev + max_sales * 0.02, f'{int(sale_prev)}',
                    ha='center', va='bottom', fontsize=12, fontweight='bold', color='#FFFFFF',
                    bbox=dict(boxstyle='round,pad=0.3', facecolor='#2C3E50', edgecolor='none', alpha=0.7))

        # 本年标签
        if sale_current > 0:
            ax.text(i, sale_current + max_sales * 0.02, f'{int(sale_current)}',
                    ha='center', va='bottom', fontsize=12, fontweight='bold', color='#FFFFFF',
                    bbox=dict(boxstyle='round,pad=0.3', facecolor='#5C1E2B', edgecolor='none', alpha=0.7))

    # 设置坐标轴
    ax.set_xticks(x_pos)
    ax.set_xticklabels(month_labels, fontsize=14, fontweight='bold', color='#E0E0E0')
    ax.tick_params(axis='y', labelsize=14, colors='#E0E0E0')

    # 设置标题
    title_main = f'{year2}年上半年各月同比{year1}年销量'

    # 分析最近的趋势
    # 找出最大增长月份（排除999的情况）
    valid_rates = [(i, rate) for i, rate in enumerate(growth_rates) if rate != 999]

    if valid_rates:
        max_growth_idx, max_growth = max(valid_rates, key=lambda x: x[1])
        max_growth_month = month_labels[max_growth_idx]

        # 生成副标题
        if max_growth >= 500:  # 极高增长率
            subtitle = f'上半年同比{year1}年增长明显，{max_growth_month}同比增长极快'
        else:
            subtitle = f'上半年同比{year1}年增长明显，{max_growth_month}同比增长最多，增长{abs(max_growth):.0f}%'
    else:
        subtitle = f'上半年同比{year1}年销量无显著变化'

    ax.set_title(f'{title_main}\n{subtitle}',
                 fontsize=20, fontweight='bold', pad=25, color='#FFFFFF',
                 y=1.05)  # 调整标题位置防止重叠

    # 添加图例
    ax.legend([line1, line2], [f'{year1}年', f'{year2}年'],
              fontsize=14, frameon=False, loc='upper left',
              labelcolor='#E0E0E0', bbox_to_anchor=(0.02, 0.98))

    # 美化坐标轴
    for spine in ax.spines.values():
        spine.set_color('#4A4A6A')
        spine.set_linewidth(2)

    # 设置网格线
    ax.grid(axis='y', alpha=0.3, linestyle='--', color='#4A4A6A')
    ax.set_axisbelow(True)

    # 添加数据来源
    latest_date = df['order_time'].max()
    latest_date_str = latest_date.strftime('%Y.%m.%d')
    source_text = f'*注：数据来源于公司销售系统，统计日期截至{latest_date_str}'
    ax.text(0.02, 0.02, source_text, transform=ax.transAxes,
            fontsize=10, color='#B0B0B0', alpha=0.7, va='bottom')

    # 调整布局
    plt.tight_layout()

    # 确保结果目录存在
    os.makedirs(RESULTS_DIR, exist_ok=True)
    # 保存图片
    output_path = os.path.join(RESULTS_DIR, '13_对比折线图.png')
    plt.savefig(output_path, dpi=300, bbox_inches='tight',
                facecolor='#1A1A2E', edgecolor='none')

    plt.show()

    # 数据分析
    print("对比折线图数据分析：")
    print(f"- 数据覆盖月份：1-12月")
    print(f"- {year2}年总销量：{sum(sales_current):.2f}元")
    print(f"- {year1}年总销量：{sum(sales_prev):.2f}元")

    # 计算整体增长率
    if sum(sales_prev) > 0:
        overall_growth = ((sum(sales_current) - sum(sales_prev)) / sum(sales_prev)) * 100
        print(f"- 整体同比增长率：{overall_growth:.1f}%")
    else:
        print("- 无法计算整体增长率，因为基准年销量为0")

    # 找出增长最快的月份
    max_growth_idx = np.argmax(growth_rates)
    print(f"- 增长最快的月份：{month_labels[max_growth_idx]}，增长率{growth_rates[max_growth_idx]:.1f}%")

    return fig, ax


# 执行代码
if __name__ == "__main__":
    try:
        fig, ax = create_comparison_line_chart()
    except Exception as e:
        print(f"图表生成失败: {e}")
        raise